<?php
/*
  2013/04/19 Search_Model_Yachinsoubaensen へ統合
  現在使用していない
*/

class Search_Model_Yachinsoubaensensyosai extends Search_Model_Yachinsouba{

	/**
	* 家賃相場（沿線）データ取得
	*/
	public function getYachinsoubaEnsenSyosaiData($params)
    {
        $db_params = array();
        $sql = $this->generateYachinsoubaEnsenSyosaiSql($params, $db_params);
        return $this->dbGetList($sql, $db_params);
    }

	/**
	* グラフデータ取得
	*/
	public function getYachinsoubaEnsenGraphData($params)
    {
        $db_params = array();
        $sql = $this->generateYachinsoubaEnsenGraphSql($params, $db_params);
        return $this->dbGetList($sql, $db_params);
    }
   /**
	* 家賃相場（沿線）を表示用に変換
	*
	* @access	public
	* @param	array ※参照渡し
	* @return	void
	*/
	public function convertYachinsoubaEnsenSyosaiData($result,$result_graph,&$data)
	{
		$display = $this->initArray();

		//グラフ縦幅の等分数
		$setcount = 5;

		foreach ($result as $record){
			//$data['ensenname'] = $record['M03_EKI_NAME'];
			$this->getMadoriAvg($record,$display);
		}
		$data['yachinsoubaensensyosai'] = $display;
		$data['graph'] = $result_graph;

		$count = 1;
		$price=Array();
		$price[0] = 0;
		foreach ($result_graph as $record){
			$price[$count] = $record['PRICE'];
			$count++;
		}
		//値段の最大値
		if (!empty($price)) {
			$max_price = max($price);
		} else {
			$max_price = 100;
		}
		//縦軸の最大値
		$gridmax = 0;

		//最大値が1万円未満
		if($max_price < 10000){
			$gridmax = 1;
		}else{

			for($i = $setcount;$i<=1000;$i=$i + $setcount){
				if($max_price < $i * 10000){
					$gridmax = $i;
					break;
				}
			}
		}
		$interval = $gridmax / $setcount;

		//縦軸ラベル
		$label = Array();
		for($i = 0;$i <= $setcount;$i++){
			$label[$i] = $i * $interval;
		}
		$data['label_y'] =implode("|",$label);
		$data['graph_range_y'] = 100 / $setcount;

		$count = 0;
		$price_prot=Array();
		foreach ($result_graph as $record){
			$price_prot[$count] = $record['PRICE'] / ($gridmax * 100);
			$count++;
		}
		$data['graph_y'] = implode(",",$price_prot);
	}

	public function getMadoriAvg($result,&$display)
	{
		$sum = $this->initArray();
		$count = $this->initArray();

		$madori = $result['MADORI'];
		$yachin = $result['YACHIN_AVG'];
		$tsuboyachin = $result['TSUBOYACHIN_AVG'];
		$shikikiin = $result['SHIKIKIN_AVG'];
		$reikin = $result['REIKIN_AVG'];

		//1R
		if($madori == '1R'){
			$display[0][0] = sprintf("%.2f", $yachin/ 10000);
			$display[1][0] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][0] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][0] = sprintf("%.2f", $reikin / 10000);

		//1K/1DK
		}else if($madori == '1K'){
			$display[0][1] = sprintf("%.2f", $yachin/ 10000);
			$display[1][1] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][1] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][1] = sprintf("%.2f", $reikin / 10000);
		//1LDK
		}else if($madori == '1LDK' ){
			$display[0][2] = sprintf("%.2f", $yachin/ 10000);
			$display[1][2] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][2] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][2] = sprintf("%.2f", $reikin / 10000);
		//2K/2DK
		}else if($madori == '2K'){
			$display[0][3] = sprintf("%.2f", $yachin/ 10000);
			$display[1][3] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][3] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][3] = sprintf("%.2f", $reikin / 10000);
		//2LDK
		}else if($madori == '2LDK' ){
			$display[0][4] = sprintf("%.2f", $yachin/ 10000);
			$display[1][4] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][4] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][4] = sprintf("%.2f", $reikin / 10000);
		//3K/3DK
		}else if($madori == '3K'){
			$display[0][5] = sprintf("%.2f", $yachin/ 10000);
			$display[1][5] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][5] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][5] = sprintf("%.2f", $reikin / 10000);
		//3K/3DK
		}else if($madori == '3LDK'){
			$display[0][6] = sprintf("%.2f", $yachin/ 10000);
			$display[1][6] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][6] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][6] = sprintf("%.2f", $reikin / 10000);
		//4k～
		}else if($madori == '4K'){
			$display[0][7] = sprintf("%.2f", $yachin/ 10000);
			$display[1][7] = sprintf("%.2f", $tsuboyachin / 10000);
			$display[2][7] = sprintf("%.2f", $shikikiin / 10000);
			$display[3][7] = sprintf("%.2f", $reikin / 10000);
		}
	}


     /**
      * 賃料（万円台）×間取り件数２次元配列の初期化
     * @access	public
     * @return display
     */
	public function initArray(){

		$display= array();

		$test1 = array(0,1,2,3);
		$test2 = array(0,1,2,3,4,5,6,7);

		foreach($test1 as $val1){
			foreach($test2 as $val2){
				$display[$val1][$val2] = 0;
			}
		}
		return $display;
	}

    /**
     * 家賃相場（沿線）データ検索用SQL文の取得
     *
     * 市区町村一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 家賃相場（沿線）データ検索用SQL
     */
	public function generateYachinsoubaEnsenSyosaiSql($params, &$db_params)
	{
		// 表示日を取得
		$ymd = $this->getYachinsoubaDate();
		if (!empty($ymd))
			$db_params['ymd'] = $ymd['YMD'];
		else
			$db_params['ymd'] = date('Ymd')-1;
		
		//$db_params['StCd'] = $params['StCd'][0];

		$sqlWhere  = $this->createWhereStCd("A02_KEY_CD", $params, $db_params);
		$sqlWhere2 = $this->createWhereStCd("M03_EKI_CD", $params, $db_params);

		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    A02_KEY_CD, ";
		$sql .= "    M03.M03_ENSEN_NAME as PAR_NAME, ";
		$sql .= "    M03.M03_EKI_NAME   as KEY_NAME, ";
		$sql .= "    ROUND(AVG(A02_YACHIN_AVG)) AS YACHIN_AVG, ";
		$sql .= "    ROUND(AVG(A02_TSUBOYACHIN_AVG)) AS TSUBOYACHIN_AVG, ";
		$sql .= "    ROUND(AVG(A02_SHIKIKIN_AVG)) AS SHIKIKIN_AVG, ";
		$sql .= "    ROUND(AVG(A02_REIKIN_AVG)) AS  REIKIN_AVG, ";
		$sql .= "    MADORI ";
		$sql .= "FROM (";
		$sql .= "  SELECT ";
		$sql .= "      A02_KEY_CD, ";
		$sql .= "      A02_YACHIN_AVG, ";
		$sql .= "      A02_TSUBOYACHIN_AVG, ";
		$sql .= "      A02_SHIKIKIN_AVG, ";
		$sql .= "      A02_REIKIN_AVG, ";
		$sql .= "      CASE ";
		$sql .= "        WHEN A02_MADORI_CD in ('10') THEN '1R'";
		$sql .= "        WHEN A02_MADORI_CD in ('11','12','13') THEN '1K'";
		$sql .= "        WHEN A02_MADORI_CD in ('14','15') THEN '1LDK'";
		$sql .= "        WHEN A02_MADORI_CD in ('20','21','22','23') THEN '2K'";
		$sql .= "        WHEN A02_MADORI_CD in ('24','25') THEN '2LDK'";
		$sql .= "        WHEN A02_MADORI_CD in ('30','31','32','33') THEN '3K'";
		$sql .= "        WHEN A02_MADORI_CD in ('34','35') THEN '3LDK'";
		$sql .= "        WHEN A02_MADORI_CD >= '40' and A02_MADORI_CD < '80' THEN '4K'";
		$sql .= "        ELSE '99'";
		$sql .= "      END AS MADORI ";
		$sql .= "  FROM ";
		$sql .= "      A02ESTATE_MKT_PRICE_DAY ";
		$sql .= "  WHERE ";
		$sql .= "      A02_DATA_KUBUN = 21 AND ";		// 沿線
		//本運用ではコメントを外す
		$sql .= "    A02_TARGET_YMD  = :ymd AND ";
		if($params['type'] == 2){
			$sql .= "  A02_TATEMONO_TYPE_CD IN ('2') AND";
		}else if($params['type'] == 3){
			$sql .= "  A02_TATEMONO_TYPE_CD IN ('1','3','4','10') AND";
		}else if($params['type'] == 4){
			$sql .= "  A02_TATEMONO_TYPE_CD IN ('5','6','7','8','9') AND";
		}else {
			$sql .= "  A02_TATEMONO_TYPE_CD IN ('1','2','3','4','5','6','7','8','9','10') AND";
		}
		//$sql .= "      A02_KEY_CD = :StCd ";
		$sql .=        $sqlWhere;
		$sql .= "  ) A02";
		$sql .= ",( SELECT DISTINCT";
		$sql .= "      M03_ENSEN_CD ";
		$sql .= "    , M03_ENSEN_NAME ";
		$sql .= "    , M03_EKI_CD ";
		$sql .= "    , M03_EKI_NAME ";
		$sql .= "   FROM M03EKI ";
		$sql .= "   WHERE ";
		$sql .=        $sqlWhere2;
		$sql .= " ) M03 ";
		$sql .= "WHERE MADORI <> '99' ";
		$sql .= "  AND A02.A02_KEY_CD = M03.M03_EKI_CD ";
		$sql .= "GROUP BY ";
		$sql .= "    A02_KEY_CD, ";
		$sql .= "    M03.M03_ENSEN_NAME, ";
		$sql .= "    M03.M03_EKI_NAME, ";
		$sql .= "    MADORI ";
		
		return $sql;
	}

    /**
     * 家賃相場（沿線）データグラフ用SQL文の取得
     *
     * 市区町村一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 家賃相場（沿線）データグラフ用SQL
     */
    public function generateYachinsoubaEnsenGraphSql($params,&$db_params)
    {
    	//$db_params['StCd'] = $params['StCd'][0];

		$sqlWhere = $this->createWhereStCd("a.A02_KEY_CD", $params, $db_params);

		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    TO_CHAR(TO_DATE(a.A02_TARGET_YM,'YYYYMM'),'YYYY/MM') AS MONTH, ";
		//賃料
		if($params['graphcode'] == 1){
			$sql .= "    AVG(a.A02_YACHIN_AVG)  AS PRICE ";
		//坪賃料
		}else if($params['graphcode'] == 2){
			$sql .= "    AVG(a.A02_TSUBOYACHIN_AVG)  AS PRICE ";
		//敷金
		}else if($params['graphcode'] == 3){
			$sql .= "    AVG(a.A02_SHIKIKIN_AVG)  AS PRICE ";
		//礼金
		}else{
			$sql .= "    AVG(a.A02_REIKIN_AVG)  AS PRICE ";
		}
		$sql .= "FROM ";
		$sql .= "    WEBLOG1_USER.A02ESTATE_MKT_PRICE_MONTH a ";
		$sql .= "WHERE ";
		$sql .= "    TO_DATE(a.A02_TARGET_YM,'YYYYMM') >= ADD_MONTHS(SYSDATE,-13) AND ";
		$sql .= "    TO_DATE(a.A02_TARGET_YM,'YYYYMM') <= ADD_MONTHS(SYSDATE,-1) AND ";
		//$sql .= "    a.A02_KEY_CD = :StCd AND ";
		$sql .=      $sqlWhere." AND ";
		//1R/1K/1DK
		if($params['madori'] == 1){
			$sql .= "    a.A02_MADORI_CD IN ('10','11','12','13') ";
		//1LDK/2K/2DK
		}else if($params['madori'] == 2){
			$sql .= "    a.A02_MADORI_CD IN ('14','15','20','21','22','23') ";
		//2LDK/3K/3DK
		}else if($params['madori'] == 3){
			$sql .= "    a.A02_MADORI_CD IN ('24','25','30','31','32','33') ";
		//3LDK/4K/4DK
		}else{
			$sql .= "    a.A02_MADORI_CD IN ('34','35','40','41','42','43') ";
		}
		$sql .= " AND ";
		$sql .= "    a.A02_DATA_KUBUN = '21' ";
		$sql .= "GROUP BY  ";
		$sql .= "    a.A02_TARGET_YM ";
		$sql .= "ORDER BY  ";
		$sql .= "    a.A02_TARGET_YM ";

	   return $sql;
    }

	public function createWhereStCd($column, $params, &$db_params) {

		$sql = "";
		$spl = "";

		for ($i=0; $i<count($params['StCd']); $i++) {
			if ($i != 0) { $spl = " OR "; }
			$db_params['stCd_' . $i] = $params['StCd'][$i];
			$sql .= $spl."( ".$column." = :stCd_".$i." )";
		}

		return " (".$sql.") ";
	}

    /**
     * 路線図CDから駅CD取得
     *
     * @access	public
     * @param	array
     * @return
     */
    public function redirectFromRosenzu(&$params)
    {
		// 駅データ検索
		$db_params = array();
		$sql = $this->generateStCdFromRosenzuSql($params, $db_params);
		$result = $this->dbGetList($sql, $db_params);

		if (empty($result)) {
			// エラー
			echo "駅コードが不正です。";
			exit;
		} else {
			// パラメータセット
			$params['SrhKbn']    = Apm_Config_Search::SRHKBN_YACHINSOUBA;
			$params['SrhType']   = Apm_Config_Search::SRHTYPE_ENSENEKI;
			$params['Detail']    = Apm_Config_Search::SRHTYPE_ENSENEKI;
			$params['PrefCd']    = array();
			$params['RailCd']    = array();
			$params['StCd']      = array();
			foreach ($result as $key => $val) {
				$params['PrefCd'][] = $val['TODOUFUKEN_CD'];
				$params['RailCd'][] = $val['ENSEN_CD'];
				$params['StCd'][]   = $val['EKI_CD'];
			}
		}

	}

    /**
     * 路線図CDから駅CD取得SQL
     *
     * @access	public
     * @param	array
     * @param	array
     * @return	string 駅CD取得用SQL
     */
    public function generateStCdFromRosenzuSql($params, &$db_params)
    {
		$db_params['r_eki_cd'] = $params['r_eki_cd'];
		// SQL
		$sql  = "";
		$sql .= " SELECT";
		$sql .= "      M03_TODOUFUKEN_CD as TODOUFUKEN_CD";
		$sql .= "     ,M03_ENSEN_CD as ENSEN_CD";
		$sql .= "     ,M03_EKI_CD as EKI_CD";
		$sql .= " FROM";
		$sql .= "      M03EKI M03";
		$sql .= " WHERE";
		$sql .= "      M03.M03_ROSENZU_EKI_CD = :r_eki_cd ";
/*
		$sql .= " SELECT";
		$sql .= "      TODOUFUKEN_CD";
		$sql .= "     ,ENSEN_CD";
		$sql .= "     ,EKI_CD";
		$sql .= " FROM";
		$sql .= "     (";
		$sql .= "         SELECT";
		$sql .= "              M03.M03_TODOUFUKEN_CD as TODOUFUKEN_CD";
		$sql .= "             ,M03.M03_ENSEN_CD as ENSEN_CD";
		$sql .= "             ,M03.M03_EKI_CD as EKI_CD";
		$sql .= "             ,T57.T57_ENSEN_SHIKUCHOUSON_CNT as ENSEN_SHIKUCHOUSON_CNT";
		$sql .= "             ,MAX(T57.T57_ENSEN_SHIKUCHOUSON_CNT) over (PARTITION BY M03.M03_TODOUFUKEN_CD, M03.M03_ROSENZU_EKI_CD) as MAX_CNT";
		$sql .= "         FROM";
		$sql .= "              M03EKI M03";
		$sql .= "             ,T57ENSEN_SHIKUCHOUSON_SUM T57";
		$sql .= "         WHERE";
		$sql .= "                 M03.M03_ROSENZU_EKI_CD = :r_eki_cd ";
		$sql .= "             AND M03.M03_TODOUFUKEN_CD = T57.T57_TODOUFUKEN_CD";
		$sql .= "             AND M03.M03_ENSEN_CD = T57.T57_ENSEN_SHIKUCHOUSON_CD";
		$sql .= "             AND T57.T57_ENSEN_SHIKUCHOUSON_FLAG = 0";
		$sql .= "             AND T57.T57_DATA_KUBUN = 0";
		$sql .= "     )";
		$sql .= " WHERE";
		$sql .= "     ENSEN_SHIKUCHOUSON_CNT = MAX_CNT";
*/
		return $sql;
	}

}

?>
